﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Dapper.Sharding
{
    internal class DuckDbTableManager : ITableManager
    {
        public DuckDbTableManager(string name, IDatabase database) : base(name, database)
        {

        }

        public override void AddColumn(string name, Type t, double length = 0, string comment = null, string columnType = null, int scale = 0)
        {
            var dbType = CsharpTypeToDbType.Create(DataBase.DbType, DataBase.DbVersion, t, length, columnType, scale);
            if (string.IsNullOrEmpty(columnType) || !columnType.ToLower().EndsWith("default 0") || !columnType.ToLower().EndsWith("default false"))
            {
#if CORE6
                if (t != null && t.IsValueType && t != typeof(DateTime) && t != typeof(DateTimeOffset) && t != typeof(DateOnly) && t != typeof(TimeOnly) && t != typeof(DateTime?) && t != typeof(DateTimeOffset?) && t != typeof(DateOnly?) && t != typeof(TimeOnly?))
#else
                if (t != null && t.IsValueType && t != typeof(DateTime) && t != typeof(DateTimeOffset) && t != typeof(DateTime?) && t != typeof(DateTimeOffset?))
#endif
                {
                    if (t != typeof(bool))
                    {
                        dbType += " DEFAULT 0";
                    }
                    else
                    {
                        dbType += " DEFAULT FALSE";
                    }
                }
            }

            DataBase.Execute($"ALTER TABLE {Name} ADD COLUMN {name} {dbType}");
        }

        public override void AddColumn(string name, string columnType, string comment = null)
        {
            DataBase.Execute($"ALTER TABLE {Name} ADD COLUMN {name} {columnType}");
        }

        public override async Task AddColumnAsync(string name, Type t, double length = 0, string comment = null, string columnType = null, int scale = 0)
        {
            var dbType = CsharpTypeToDbType.Create(DataBase.DbType, DataBase.DbVersion, t, length, columnType, scale);
            if (string.IsNullOrEmpty(columnType) || !columnType.ToLower().EndsWith("default 0") || !columnType.ToLower().EndsWith("default false"))
            {
#if CORE6
                if (t != null && t.IsValueType && t != typeof(DateTime) && t != typeof(DateTimeOffset) && t != typeof(DateOnly) && t != typeof(TimeOnly) && t != typeof(DateTime?) && t != typeof(DateTimeOffset?) && t != typeof(DateOnly?) && t != typeof(TimeOnly?))
#else
                if (t != null && t.IsValueType && t != typeof(DateTime) && t != typeof(DateTimeOffset) && t != typeof(DateTime?) && t != typeof(DateTimeOffset?))
#endif
                {
                    if (t != typeof(bool))
                    {
                        dbType += " DEFAULT 0";
                    }
                    else
                    {
                        dbType += " DEFAULT FALSE";
                    }
                }
            }

            await DataBase.ExecuteAsync($"ALTER TABLE {Name} ADD COLUMN {name} {dbType}");
        }

        public override void CommentColumn(string name, string comment)
        {
            throw new NotImplementedException();
        }

        public override void CreateIndex(string name, string columns, IndexType indexType)
        {
            string sql = null;
            switch (indexType)
            {
                case IndexType.Normal: sql = $"CREATE INDEX {name} ON {Name} ({columns});"; break;
                case IndexType.Unique: sql = $"CREATE UNIQUE INDEX {name} ON {Name} ({columns});"; break;
            }
            DataBase.Execute(sql);
        }

        public override void DropColumn(string name)
        {
            DataBase.Execute($"ALTER TABLE {Name} DROP IF EXISTS {name};");
        }

        public override async Task DropColumnAsync(string name)
        {
            await DataBase.ExecuteAsync($"ALTER TABLE {Name} DROP IF EXISTS {name};");
        }

        public override void DropIndex(string name)
        {
            DataBase.Execute($"DROP INDEX IF EXISTS {name}");
        }

        public override List<ColumnEntity> GetColumnEntityList(TableEntity tb = null, bool firstCharToUpper = false)
        {
            tb ??= new TableEntity();

            var list = new List<ColumnEntity>();
            var data = DataBase.Query($"DESCRIBE {Name}");

            foreach (var item in data)
            {
                var model = new ColumnEntity();
                if (firstCharToUpper)
                {
                    model.Name = ((string)item.column_name).FirstCharToUpper();
                }
                else
                {
                    model.Name = (string)item.column_name;
                }

                if (item.key == "PRI")
                {
                    tb.PrimaryKey = model.Name;
                    model.IsPrimaryKey = true;
                }

                var columnType = item.column_type;//数据类型
                var array = columnType.Split('(');
                var t = array[0];
                model.DbType = t;
                var map = DbCsharpTypeMap.DuckDbMap.FirstOrDefault(f => f.DbType == t);

                if (map != null)
                {
                    model.CsStringType = map.CsStringType;
                    model.CsType = map.CsType;
                }
                else
                {
                    model.CsStringType = "object";
                    model.CsType = typeof(object);
                }

                if (array.Length == 2)
                {
                    var length = array[1].Split(')')[0];
                    model.DbLength = length;
                    if (length.Contains(","))
                    {
                        var scaleArr = length.Split(',');
                        if (scaleArr.Length > 1)
                        {
                            model.Length = Convert.ToInt32(scaleArr[0]);
                            model.Scale = Convert.ToInt32(scaleArr[1]);
                        }
                    }

                    if (model.Scale <= 0 || !model.Scale.ToString().EndsWith("0"))
                    {
                        model.Length = Convert.ToDouble(length.Replace(',', '.'));
                        model.Scale = 0;
                    }
                }
                else
                {
                    model.Length = 0;
                    model.DbLength = "0";
                }

                list.Add(model);
            }

            return list;

        }

        public override async Task<List<ColumnEntity>> GetColumnEntityListAsync(TableEntity tb = null, bool firstCharToUpper = false)
        {
            if (tb == null)
                tb = new TableEntity();

            var list = new List<ColumnEntity>();
            var data = await DataBase.QueryAsync($"DESCRIBE {Name}");

            foreach (var item in data)
            {
                var model = new ColumnEntity();
                if (firstCharToUpper)
                {
                    model.Name = ((string)item.column_name).FirstCharToUpper();
                }
                else
                {
                    model.Name = (string)item.column_name;
                }

                if (item.key == "PRI")
                {
                    tb.PrimaryKey = model.Name;
                }

                var columnType = item.column_type;//数据类型
                var array = columnType.Split('(');
                var t = array[0];
                model.DbType = t;
                var map = DbCsharpTypeMap.DuckDbMap.FirstOrDefault(f => f.DbType == t);

                if (map != null)
                {
                    model.CsStringType = map.CsStringType;
                    model.CsType = map.CsType;
                }
                else
                {
                    model.CsStringType = "object";
                    model.CsType = typeof(object);
                }

                if (array.Length == 2)
                {
                    var length = array[1].Split(')')[0];
                    model.DbLength = length;
                    if (length.Contains(","))
                    {
                        var scaleArr = length.Split(',');
                        if (scaleArr.Length > 1)
                        {
                            model.Length = Convert.ToInt32(scaleArr[0]);
                            model.Scale = Convert.ToInt32(scaleArr[1]);
                        }
                    }

                    if (model.Scale <= 0 || !model.Scale.ToString().EndsWith("0"))
                    {
                        model.Length = Convert.ToDouble(length.Replace(',', '.'));
                        model.Scale = 0;
                    }
                }
                else
                {
                    model.Length = 0;
                    model.DbLength = "0";
                }

                list.Add(model);
            }

            return list;
        }

        public override List<IndexEntity> GetIndexEntityList()
        {
            var list = new List<IndexEntity>();
            //主键索引
            var data1 = DataBase.Query($"SELECT * FROM duckdb_constraints() WHERE schema_name='main' AND table_name='{Name}'");
            if (data1.Count > 0)
            {

                var model = new IndexEntity
                {
                    Name = data1[0].constraint_column_names[0],
                    Columns = data1[0].constraint_column_names[0],
                    Type = IndexType.PrimaryKey
                };

                list.Add(model);
            }

            //非主键索引
            var data2 = DataBase.Query($"SELECT * FROM duckdb_indexes() WHERE schema_name='main' AND table_name='{Name}'");
            foreach (var item in data2)
            {
                var model = new IndexEntity
                {
                    Name = item.index_name
                };

                if (item.is_unique)
                {
                    model.Type = IndexType.Unique;
                }
                else
                {
                    model.Type = IndexType.Normal;
                }

                //string a = "";
                model.Columns = item.sql.Split('(', ')')[1];
                list.Add(model);
            }
            return list;
        }

        public override void ModifyColumn(string name, Type t, double length = 0, string comment = null, string columnType = null, int scale = 0)
        {
            var dbType = CsharpTypeToDbType.Create(DataBase.DbType, DataBase.DbVersion, t, length, columnType, scale);
            DataBase.Execute($"ALTER TABLE {Name} ALTER COLUMN {name} TYPE {dbType}");
        }

        public override void ModifyColumn(string name, string columnType, string comment = null)
        {
            DataBase.Execute($"ALTER TABLE {Name} ALTER COLUMN {name} TYPE {columnType}");
        }

        public override async Task ModifyColumnAsync(string name, Type t, double length = 0, string comment = null, string columnType = null, int scale = 0)
        {
            var dbType = CsharpTypeToDbType.Create(DataBase.DbType, DataBase.DbVersion, t, length, columnType, scale);
            await DataBase.ExecuteAsync($"ALTER TABLE {Name} ALTER COLUMN {name} TYPE {dbType}");
        }

        public override async Task ModifyColumnAsync(string name, string columnType, string comment = null)
        {
            await DataBase.ExecuteAsync($"ALTER TABLE {Name} ALTER COLUMN {name} TYPE {columnType}");
        }

        public override void ReNameColumn(string name, string newName, Type t = null, double length = 0, string comment = null, string columnType = null, int scale = 0)
        {
            DataBase.Execute($"ALTER TABLE {Name} RENAME COLUMN {name} TO {newName}");
        }
    }
}
